package com.first.dao.impl;

import com.first.dao.BookDao;
import com.first.entity.BType;
import com.first.entity.Book;
import com.first.util.DBUtil;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class BookDaoImpl implements BookDao {
    /**
     * 新增类型
     * @param bType
     * @return
     */
    @Override
    public int addType(BType bType) {
        String sql = "insert into b_type values(null,?)";
        int i = DBUtil.update(sql, bType.getType());
            if(i>0){
                return  i;
            }else {
                return  -1;
            }
    }

    /**
     * 查询类别下的所有书
     * @param book
     * @return
     */
    @Override
    public List<Map> queryBookByTypeid(Book book) {
        String sql = "select * from book where typeid = ?";
        List<Map> maps = DBUtil.queryListMap(sql, book.getTypeid());
        if(maps.size()>0){
            return  maps;
        }
        return null;
    }

    /**
     * 查询所有类型
     * @param book
     * @return
     */

    @Override
    public List<Map> queryAllBookType(Book book) {
        String sql = "select sum(count),t.type ,t.typeid from b_type t LEFT JOIN book b on t.typeid=b.typeid  GROUP BY t.typeid";
        List<Map> maps = DBUtil.queryListMap(sql);
       if (maps.size()>0) {
           return maps;
       }
       return null;
    }

    /**
     * 查询所有书籍
     *
     * @param book
     * @return
     */
    @Override
    public List<Map> queryBooks(Book book) {

        StringBuilder sql = new StringBuilder("SELECT\n" +
                "\tbook.bookid, \n" +
                "\tbook.bookname, \n" +
                "\tbook.author, \n" +
                "\tbook.count, \n" +
                "\tbook.state, \n" +
                "\tbook.typeid, \n" +
                "\tbook.press, \n" +
                "\tbook.inprice, \n" +
                "\tbook.outprice, \n" +
                "\tb_type.type, \n" +
                "\turl.url\n" +
                "FROM\n" +
                "\tbook\n" +
                "\tLEFT JOIN\n" +
                "\tb_type\n" +
                "\tON \n" +
                "\t\tbook.typeid = b_type.typeid\n" +
                "\tLEFT JOIN\n" +
                "\turl\n" +
                "\tON \n" +
                "\t\tbook.urlid = url.urlid  where 1=1");
        if (book.getBookname()!=null || "null".equals(book.getBookname())) {
            sql.append(" and book.bookname like '%"+book.getBookname()+"%'");
        }
//            if (book.getBookid()!=0 || "null".equals(book.getBookid())) {
//
//            sql.append(" and book.bookid like '%"+book.getBookid()+"%'");
//        }
//        else
        if (book.getAuthor()!=null || "null".equals(book.getAuthor())) {

            sql.append(" and book.author like '%"+book.getAuthor()+"%'");
        }
        if (book.getPress()!=null || "null".equals(book.getPress())) {

            sql.append(" and book.press like '%"+book.getPress()+"%'");
        }
        sql.append(" order by bookid limit ?,? ");
        List<Map> list = DBUtil.queryListMap(sql.toString(), (book.getPage() - 1) * book.getLimit(), book.getLimit());


        if (list.size()>0) {
            return list;
        }else {
            return null;
        }
    }



    /**
     * 查询书籍的数量
     *
     * @param book
     * @return
     */
    @Override
    public int getBookCount(Book book) {

        StringBuilder sql = new StringBuilder("SELECT\n" +
                "\tcount(*)\n" +
                "FROM\n" +
                "\tbook\n" +
                "\tLEFT JOIN\n" +
                "\tb_type\n" +
                "\tON \n" +
                "\t\tbook.typeid = b_type.typeid\n" +
                "\tLEFT JOIN\n" +
                "\turl\n" +
                "\tON \n" +
                "\t\tbook.urlid = url.urlid  where 1=1");
        if (book.getBookname()!=null || "null".equals(book.getBookname())) {

            sql.append(" and book.bookname like '%"+book.getBookname()+"%'");
        } else if (book.getBookid()!=0 || "null".equals(book.getBookid())) {

            sql.append(" and book.bookid like '%"+book.getBookid()+"%'");
        }
        else if (book.getAuthor()!=null || "null".equals(book.getAuthor())) {

            sql.append(" and book.author like '%"+book.getAuthor()+"%'");
        }else if (book.getPress()!=null || "null".equals(book.getPress())) {

            sql.append(" and book.press like '%"+book.getPress()+"%'");
        }
        sql.append(" order by bookid");
        List<List> list = DBUtil.queryList(sql.toString());
        if (list.size()>0) {

            Object o = list.get(0).get(0);
            return Integer.parseInt(o+"");
        }else {
            return 0;
        }
    }

    /**
     * 新增书籍
     *
     * @param book
     * @return
     */
    @Override
    public int addBook(Book book) {
        String sql = "insert into book values(null,?,?,0,?,?,?,?,null,?)";
        int i = DBUtil.getPrimaryKey(sql,book.getBookname(),book.getAuthor(),book.getState(),book.getTypeid(),book.getPress(),book.getInprice(),book.getOutprice());
        return i;
    }

    @Override
    public int updateBookById(Book book) {
        String sql="update book set bookname=?,author=?,state=?,typeid=?,press=?,inprice=?,outprice=? where bookid=?";
        int i = DBUtil.update(sql,book.getBookname(),book.getAuthor(),book.getState(),book.getTypeid(),book.getPress(),book.getInprice(),book.getOutprice(),book.getBookid());
        return i;
    }

    @Override
    public int delBookById(Integer bookid) {
        String sql="delete from book where bookid=?";
        int i = DBUtil.update(sql, bookid);
        return i;
    }


}
